package com.china08.yxyapi.repository.db;

import java.util.List;

import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import com.china08.yxyapi.entity.db.lessons.YxyLessonsTopicLesson;

public interface YxyLessonsTopicLessonRepository extends JpaRepository<YxyLessonsTopicLesson, String> {

    Integer countBySchoolId(String schoolId);

    // 查询草稿
    @Query(value = " select t1 from YxyLessonsTopicLesson t1 where t1.schoolId=:schoolId and t1.status = :status order by t1.createdDate desc")
    Page<YxyLessonsTopicLesson> findBySchoolIdAndStatus(@Param("schoolId") String schoolId,
            @Param("status") Integer status, Pageable pageable);

    @Query(value = " select t1 from YxyLessonsTopicLesson t1 where t1.schoolId=:schoolId and t1.status = :status and (t1.catalogId1 = :catalogId or t1.catalogId2 = :catalogId or t1.catalogId3 = :catalogId) and t1.name like CONCAT('%',:keyName,'%') order by t1.createdDate desc")
    Page<YxyLessonsTopicLesson> findBySchoolIdAndStatusAndCategoryIdAndNameLike(@Param("schoolId") String schoolId,
            @Param("status") Integer status, @Param("catalogId") String catalogId, @Param("keyName") String keyName,
            Pageable pageable);

    @Query(value = " select t1 from YxyLessonsTopicLesson t1 where t1.schoolId=:schoolId and t1.status = :status and t1.name like CONCAT('%',:keyName,'%') order by t1.createdDate desc")
    Page<YxyLessonsTopicLesson> findBySchoolIdAndStatusAndNameLike(@Param("schoolId") String schoolId,
            @Param("status") Integer status, @Param("keyName") String keyName, Pageable pageable);

    Integer countByCatalogId1(String id1);

    Integer countByCatalogId1AndCatalogId2(String id1, String id2);

    Integer countByCatalogId1AndCatalogId2AndCatalogId3(String id1, String id2, String id3);

    List<YxyLessonsTopicLesson> findByCatalogId3(String idThree);

    // 查询上架-下架
    @Query(value = " select t1 from YxyLessonsTopicLesson t1 where t1.schoolId=:schoolId and t1.status=2 and t1.isVisible = :isVisible order by t1.createdDate desc")
    Page<YxyLessonsTopicLesson> findBySchoolIdAndIsVisible(@Param("schoolId") String schoolId,
            @Param("isVisible") Integer isVisible, Pageable pageable);

    @Query(value = " select t1 from YxyLessonsTopicLesson t1 where t1.schoolId=:schoolId and t1.status=2 and  t1.isVisible = :isVisible and (t1.catalogId1 = :catalogId or t1.catalogId2 = :catalogId or t1.catalogId3 = :catalogId) and t1.name like CONCAT('%',:keyName,'%') order by t1.createdDate desc")
    Page<YxyLessonsTopicLesson> findBySchoolIdAndIsVisibleAndCategoryIdAndNameLike(@Param("schoolId") String schoolId,
            @Param("isVisible") Integer isVisible, @Param("catalogId") String catalogId,
            @Param("keyName") String keyName, Pageable pageable);

    @Query(value = " select t1 from YxyLessonsTopicLesson t1 where t1.schoolId=:schoolId and t1.status=2 and  t1.isVisible = :isVisible and t1.name like CONCAT('%',:keyName,'%') order by t1.createdDate desc")
    Page<YxyLessonsTopicLesson> findBySchoolIdAndIsVisibleAndNameLike(@Param("schoolId") String schoolId,
            @Param("isVisible") Integer isVisible, @Param("keyName") String keyName, Pageable pageable);

    YxyLessonsTopicLesson findById(String id);

    @Query(value = " select t1 from YxyLessonsTopicLesson t1 where t1.schoolId=:schoolId and t1.status >=0 order by t1.createdDate desc")
    Page<YxyLessonsTopicLesson> findAllBySchoolId(@Param("schoolId") String schoolId, Pageable pageable);

    @Query(value = " select t1 from YxyLessonsTopicLesson t1 where t1.schoolId=:schoolId and t1.status >=0 and (t1.catalogId1 = :catalogId or t1.catalogId2 = :catalogId or t1.catalogId3 = :catalogId) and t1.name like CONCAT('%',:keyName,'%') order by t1.createdDate desc")
    Page<YxyLessonsTopicLesson> findBySchoolIdAndCategoryIdAndNameLike(@Param("schoolId") String schoolId,
            @Param("catalogId") String catalogId, @Param("keyName") String keyName, Pageable pageable);

    @Query(value = " select t1 from YxyLessonsTopicLesson t1 where t1.schoolId=:schoolId and t1.status >=0 and t1.name like CONCAT('%',:keyName,'%') order by t1.createdDate desc")
    Page<YxyLessonsTopicLesson> findBySchoolIdAndNameLike(@Param("schoolId") String schoolId,
            @Param("keyName") String keyName, Pageable pageable);

    Page<YxyLessonsTopicLesson> findByNameContaining(String name, Pageable pageable);

    List<YxyLessonsTopicLesson> findTop2ByAuthor(String aaName);

    List<YxyLessonsTopicLesson> findTop2ByCatalogId3AndStatusAndIsVisible(String catalogId3, int i, int j);

    List<YxyLessonsTopicLesson> findTop2ByAuthorAndStatusAndIsVisible(String aaName, int i, int j);

    YxyLessonsTopicLesson findBySchoolIdAndName(String schoolId, String name);

    Page<YxyLessonsTopicLesson> findByStatusAndIsVisibleAndNameContaining(int i, int j, String name, Pageable pageable);

    List<YxyLessonsTopicLesson> findTop2ByCatalogId3AndStatusAndIsVisibleOrderByVvDesc(String catalogId3, int i, int j);

    List<YxyLessonsTopicLesson> findTop2ByAuthorAndStatusAndIsVisibleOrderByVvDesc(String aaName, int i, int j);

    List<YxyLessonsTopicLesson> findTop8ByStatusAndIsVisibleOrderByLastModifiedDateDesc(int i, int j);

}
